package com.huayue.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Component;

import com.huayue.entity.HousekeepOrder;
import com.huayue.util.PageHelper;
import com.mysql.DBHelper;

/**
 * 房务订单
 */
@Component
public class HousekeepOrderDao implements BaseDao<HousekeepOrder> {
    @Override
    public int insert(HousekeepOrder entity) {

        return DBHelper.executeUpdate(
            "INSERT INTO housekeep_order (ordernumber, employee_id_apply, workdate_begin) VALUES (?,?,?)",
            entity.getOrdernumber(), entity.getEnployee_id_apply(), entity.getWorkdate_begin());
    }

    @Override
    public int update(HousekeepOrder entity) {
        return DBHelper.executeUpdate(
            "UPDATE housekeep_order SET ordernumber=?, employee_id_ratify=?, employee_id_apply=?, workdate_begin=?, workdate_end=?, WHERE id=?",
            entity.getOrdernumber(), entity.getEnployee_id_ratify(), entity.getEnployee_id_apply(),
            entity.getWorkdate_begin(), entity.getWorkdate_end(), entity.getId());
    }

    @Override
    public int delete(HousekeepOrder entity) {
        return DBHelper.executeUpdate("update housekeep_order set is_status=9 where id=?", entity.getId());
    }

    @Override
    public List<Map<String, Object>> findAll(Object... obj) {
        return DBHelper
            .executeQuery("select * from housekeep_order h INNER JOIN employee e on h.employee_id_apply = e.id");
    }

    @Override
    public List<Map<String, Object>> findPage(PageHelper page, Object... obj) {
        HousekeepOrder housekeepOrder = (HousekeepOrder)obj[0];
        String sql = "select ho.id,ho.ordernumber,e.realname aname,\n"
            + "em.realname rname,ho.workdate_begin,ho.workdate_end,ho.is_status from housekeep_order ho \n"
            + "LEFT JOIN employee e on e.id = ho.employee_id_apply \n"
            + "LEFT JOIN employee em on em.id = ho.employee_id_ratify \n"
            + "LEFT JOIN housekeep_order_details d on ho.id = d.housekeep_order_id  where ho.is_deleted = 0";
        if (obj[1] != null && obj[2] != null) {
            sql += " and ho.workdate_begin BETWEEN '" + obj[1] + "' AND '" + obj[2] + "' ";
        }
        if (housekeepOrder.getIs_status() != 0) {
            sql += " and ho.is_status = " + housekeepOrder.getIs_status();
        } else {
            sql += " and ho.is_status != 9";
        }
        if (housekeepOrder.getOrdernumber() != null && !"".equals(housekeepOrder.getOrdernumber())) {
            sql +=
                " and concat(e.realname,em.realname,ho.ordernumber) like '%" + housekeepOrder.getOrdernumber() + "%'";
        }
        return DBHelper.executeQuery(sql + " GROUP BY ho.id ORDER BY ho.id DESC limit ?,?;", page.getBegin(),
            page.getPageSize());

        // return DBHelper.executeQuery("select o.id,\n" +
        // "o.ordernumber,\n" +
        // "(select realname from employee where id=employee_id_apply) aname,\n" +
        // "(select realname from employee where id = employee_id_ratify) rname,\n" +
        // "o.workdate_begin,o.workdate_end,o.is_status\n" +
        // "from HousekeepOrder o LEFT JOIN housekeep_order_details d on o.id = d.housekeep_order_id GROUP BY o.id " +
        // "limit ?,? ",page.getBegin(),page.getPageSize());
    }

    @Override
    public Map<String, Object> findOne(HousekeepOrder entity) {
        return DBHelper.executeQuery("select * from housekeep_order where id=?", entity.getId()).get(0);
    }

    @Override
    public int count(Object... obj) {
        HousekeepOrder housekeepOrder = (HousekeepOrder)obj[0];
        String sql = "select count(*) counts from housekeep_order ho \n"
            + "LEFT JOIN employee e on e.id = ho.employee_id_apply \n"
            + "LEFT JOIN employee em on em.id = ho.employee_id_ratify \n"
            + "LEFT JOIN housekeep_order_details d on ho.id = d.housekeep_order_id  where ho.is_deleted = 0";
        if (obj[1] != null && obj[2] != null) {
            sql += " and ho.workdate_begin BETWEEN '" + obj[1] + "' AND '" + obj[2] + "' ";
        }
        if (housekeepOrder.getIs_status() != 0) {
            sql += " and ho.is_status = " + housekeepOrder.getIs_status();
        } else {
            sql += " and ho.is_status != 9";
        }
        if (housekeepOrder.getOrdernumber() != null && !"".equals(housekeepOrder.getOrdernumber())) {
            sql +=
                " and concat(e.realname,em.realname,ho.ordernumber) like '%" + housekeepOrder.getOrdernumber() + "%'";
        }
        List<Map<String, Object>> list = DBHelper.executeQuery(sql + " GROUP BY ho.id ORDER BY ho.id DESC ");
        if (list.size() == 0) {
            return 0;
        }
        return Integer.valueOf(list.get(0).get("counts").toString());
    }

    public int findMaxId() {
        return Integer
            .valueOf(DBHelper.executeQuery("select max(id) maxid from housekeep_order").get(0).get("maxid").toString());
    }

    public int changeStatus(HousekeepOrder housekeepOrder, Integer number) {
        // 修改房间状态
        String sqlRoomStatus = "update room_status set status_id= 1 where room_id in (select d.room_id from \n"
            + " (select room_id from housekeep_order_details rd where rd.housekeep_order_id = ?) d)";
        DBHelper.executeUpdate(sqlRoomStatus, housekeepOrder.getId());

        // 修改订单状态
        String sqlOrder = "update housekeep_order set employee_id_ratify=?,is_status=1,workdate_end=? where id=?";
        return DBHelper.executeUpdate(sqlOrder, housekeepOrder.getEnployee_id_ratify(), new Date(),
            housekeepOrder.getId());
    }

    // 作废单
    public int checkOut(HousekeepOrder housekeepOrder) {
        String sql = "update housekeep_order set employee_id_ratify=?,is_status=9,workdate_end=? where id=?";
        return DBHelper.executeUpdate(sql, housekeepOrder.getEnployee_id_ratify(), new Date(), housekeepOrder.getId());
    }

    public int minDepotNumber() {
        String sql =
            "select min(real_number) min_number from depot d left join product p on d.product_id=p.id where p.product_type_id=10";
        List<Map<String, Object>> list = DBHelper.executeQuery(sql);
        if (list.size() == 0) {
            return 0;
        }
        return Integer.valueOf(list.get(0).get("min_number").toString());
    }

    /**
     * 减去布草数量
     * 
     * @return
     */
    public int changeDepotNumber(Integer number) {
        String sqlProduct =
            "update depot set real_number = real_number - ? where product_id in (select pd.id from (select id from product where product_type_id=10)pd)";
        return DBHelper.executeUpdate(sqlProduct, number);
    }
}
